package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.shared.entity.Goods;

public class GoodsDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<Goods> list = new ArrayList<Goods>();
		if (rs != null) {
			while (rs.next()) {
				Goods g = new Goods();
				g.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				g.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
				g.setVendorName(rs.getString("vendorName") == null ? "" : rs.getString("vendorName").trim());
				g.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				g.setUnit(rs.getString("unit") == null ? "" : rs.getString("unit").trim());
				g.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				g.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				g.setDepCode(rs.getString("depCode") == null ? "" : rs.getString("depCode").trim());
				g.setClsCode(rs.getString("clsCode") == null ? "" : rs.getString("clsCode").trim());
				g.setVendorCode(rs.getString("vendorCode") == null ? "" : rs.getString("vendorCode").trim());
				g.setPrice(rs.getBigDecimal("price"));
				g.setjPrice(rs.getBigDecimal("jPrice"));
				g.setPsPrice(rs.getBigDecimal("psPrice"));
				list.add(g);
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Goods> getGoods() {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT g.pluCode,g.pluName,g.barCode,g.clsCode,g.depCode,g.unit,g.vendorCode,g.price,g.jPrice,g.psPrice," + "(SELECT vendorName FROM " + hxDbName
				+ "..vendor v WHERE v.vendorCode = g.vendorCode) as vendorName," + "(SELECT depName FROM " + hxDbName + "..dept d WHERE d.depCode = g.depCode) as depName," + "(SELECT ClsName FROM "
				+ hxDbName + "..gclass c WHERE c.clsCode = g.clsCode ) as clsName FROM " + hxDbName + "..Goods g";
		List<?> list = super.query(sql);
		return (List<Goods>) list;
	}

	@SuppressWarnings("unchecked")
	public List<Goods> getGoodsByCode(String code) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT g.pluCode,g.pluName,g.barCode,g.clsCode,g.depCode,g.unit,g.vendorCode,g.price,g.jPrice,g.psPrice," + "(SELECT vendorName FROM " + hxDbName
				+ "..vendor v WHERE v.vendorCode = g.vendorCode) as vendorName," + "(SELECT depName FROM " + hxDbName + "..dept d WHERE d.depCode = g.depCode) as depName," + "(SELECT ClsName FROM "
				+ hxDbName + "..gclass c WHERE c.clsCode = g.clsCode ) as clsName FROM " + hxDbName + "..Goods g WHERE g.PluCode like '%" + code + "%' OR g.BarCode like '%" + code
				+ "%' OR g.pluName like  '%" + code + "%' OR g.pluMN like '%" + code + "%'";
		List<?> list = super.query(sql);
		return (List<Goods>) list;
	}

	public List<Goods> getGoodsByPluCodeOrBarCode(String code) {
		List<Goods> goodsList = new ArrayList<Goods>();
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT pluCode,pluName,barCode,pluMN FROM " + hxDbName + "..goods WHERE barCode LIKE '%" + code + "%' OR pluCode LIKE '%" + code + "%'  OR pluMN LIKE '%" + code + "%'";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Goods g = new Goods();
				g.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				g.setPluMN(rs.getString("pluMN") == null ? "" : rs.getString("pluMN").trim());
				g.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				g.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				goodsList.add(g);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// DBConnection.getInstance().release(conn);
			}
		}
		return goodsList;
	}

	public List<Goods> getGoodsByPluMN(String pluMN) {
		List<Goods> goodsList = new ArrayList<Goods>();
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT pluCode,pluName,barCode,pluMN FROM " + hxDbName + "..goods WHERE pluMN LIKE '%" + pluMN + "%'";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Goods g = new Goods();
				g.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				g.setPluMN(rs.getString("pluMN") == null ? "" : rs.getString("pluMN").trim());
				g.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				g.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				goodsList.add(g);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return goodsList;

	}

	@SuppressWarnings("unchecked")
	public List<Goods> getGoodsByCodeDept(String code, String deptCode) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT g.pluCode,g.pluName,g.barCode,g.clsCode,g.depCode,g.unit,g.vendorCode,g.price,g.jPrice,g.psPrice," + "(SELECT vendorName FROM " + hxDbName
				+ "..vendor v WHERE v.vendorCode = g.vendorCode) as vendorName," + "(SELECT depName FROM " + hxDbName + "..dept d WHERE d.depCode = g.depCode) as depName," + "(SELECT ClsName FROM "
				+ hxDbName + "..gclass c WHERE c.clsCode = g.clsCode ) as clsName FROM " + hxDbName + "..Goods g WHERE (g.PluCode like '%" + code + "%' OR g.BarCode like '%" + code
				+ "%' OR g.pluName like  '%" + code + "%' OR g.pluMN like '%" + code + "%') AND g.depCode = '" + deptCode + "'";
		List<?> list = super.query(sql);
		return (List<Goods>) list;
	}
}
